package com.service.db.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.service.bean.company.Company;
import com.service.bean.notice.NoticeMessage;
import com.service.bean.notice.NoticeRulePersonBean;
import com.service.bean.notice.NoticeTemplate;
import com.service.db.DbConnectionManager;
import com.service.db.PooledConnection;
import com.service.other.Logger;

public class DbNotice {

	public DbNotice(Company company) {
		super();
		this.company = company;
	}

	public Company company;
	
	public List<NoticeTemplate> queryMessageTeamplate(String GPSNO,
			String subType) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<NoticeTemplate> list = new ArrayList<NoticeTemplate>();
		try {

			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select rnfe.NoticeRuleId, rnr.NoticeCategory,rnr.NoticeTimes,rnr.TimeSpan,rnr.NoticePersons,rnt.TemplateName,rnt.TemplateText "
							+ " from ref_NoticeRuleEquipment  rnfe "
							+ " inner join ref_NoticeRuleAlarm rnra on rnfe.NoticeRuleId=rnra.NoticeRuleId  "
							+ " inner join ref_NoticeRule rnr on rnfe.NoticeRuleId=rnr.NoticeRuleId and rnr.Status = 'Active' "
							+ " inner join ref_NoticeTemplate rnt on rnr.NoticeTemplateId=rnt.TemplateId "
							+ " inner join crc_Equipment ce on rnfe.EquipmentId=ce.EquipmentId "
							+ " where ce.GPSNO = ? and rnra.AlarmCategory = ?");
			pstmt.setString(1, GPSNO);
			pstmt.setString(2, subType);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NoticeTemplate noticetemplate = new NoticeTemplate();
				noticetemplate.setNoticeRuleId(rs.getString("NoticeRuleId"));
				noticetemplate.setNoticeCategory(rs.getString("NoticeCategory"));
				noticetemplate.setNoticeTimes(rs.getInt("NoticeTimes"));
				noticetemplate.setTimeSpan(rs.getInt("TimeSpan"));
				noticetemplate.setNoticePersons(rs.getString("NoticePersons"));
				noticetemplate.setTemplateName(rs.getString("TemplateName"));
				noticetemplate.setTemplateText(rs.getString("TemplateText"));
				list.add(noticetemplate);
			}
		} catch (Exception e) {
			
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return list;
	}

	public String getAlertName(String refType, String refValue,
			String companycode) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String refText = "";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("  select RefText from ref_SysPara where RefType = ? and RefValue = ? and CompanyCode = ? ");
			pstmt.setString(1, refType);
			pstmt.setString(2, refValue);
			pstmt.setString(3, companycode);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				refText = rs.getString("RefText");
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return refText;
	}


	public List<NoticeRulePersonBean> queryNoticeRulePerson(
			String NoticeRuleId) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<NoticeRulePersonBean> list = new ArrayList<NoticeRulePersonBean>();
		try {

			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select * from ref_NoticeRulePerson where NoticeRuleId = ?");
			pstmt.setString(1, NoticeRuleId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NoticeRulePersonBean noticeRulePersonBean = new NoticeRulePersonBean();
				noticeRulePersonBean.setNoticeRuleId(NoticeRuleId.toString());
				noticeRulePersonBean.setPersonName(rs.getString("PersonName"));
				noticeRulePersonBean.setTelPhone(rs.getString("TelPhone"));
				noticeRulePersonBean.setId(rs.getInt("Id"));
				noticeRulePersonBean.setEmail(rs.getString("Email"));
				list.add(noticeRulePersonBean);
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return list;
	}

	public void insert(NoticeMessage noticeMessage) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		try {
			String sql = "insert into crc_NoticeMessage (NoticeCategory,MsgType,Sender,"
					+ "ReceiverId,ReceiverName,ReceiverMail,ReceiverPhone,MessageText,"
					+ "IsSend,SendTimes,TimeSpan,HasSendTimes,NextSendTime,CompanyCode,MsgTitle,ReceiverAccount,AddTime)"
					+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);
			if (noticeMessage != null) {
				pstmt.setString(1, noticeMessage.getNoticeCategory());
				pstmt.setString(2, noticeMessage.getMsgType());
				pstmt.setString(3, noticeMessage.getSender());
				pstmt.setString(4, noticeMessage.getReceiverId());
				pstmt.setString(5, noticeMessage.getReceiverName());
				pstmt.setString(6, noticeMessage.getReceiverMail());
				pstmt.setString(7, noticeMessage.getReceiverPhone());
				pstmt.setString(8, noticeMessage.getMessageText());
				pstmt.setInt(9, noticeMessage.getIsSend());
				pstmt.setInt(10, noticeMessage.getSendTimes());
				pstmt.setInt(11, noticeMessage.getTimeSpan());
				pstmt.setInt(12, noticeMessage.getHasSendTimes());
				pstmt.setInt(13, noticeMessage.getHasSendTimes());
				pstmt.setString(14, noticeMessage.getCompanyCode());
				pstmt.setString(15, noticeMessage.getMsgTitle());
				pstmt.setString(16, noticeMessage.getReceiverAccount());
				pstmt.setTimestamp(17, noticeMessage.getAddTime());
			}
			pstmt.executeUpdate();
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}

	public List<NoticeMessage> queryNoticeMessageNow() {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<NoticeMessage> list = new ArrayList<NoticeMessage>();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select top 100 * from crc_NoticeMessage cnt where cnt.SendTimes>cnt.HasSendTimes and cnt.NextSendTime<(select getdate() as today) and NoticeCategory in('Email','Message')");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NoticeMessage noticeMessage = new NoticeMessage();
				noticeMessage.setMsgId(rs.getInt("MsgId"));
				noticeMessage.setNoticeCategory(rs.getString("NoticeCategory"));
				noticeMessage.setMsgType(rs.getString("msgType"));
				noticeMessage.setSender(rs.getString("Sender"));
				noticeMessage.setReceiverId(rs.getString("ReceiverId"));
				noticeMessage.setReceiverName(rs.getString("ReceiverName"));
				noticeMessage.setReceiverMail(rs.getString("ReceiverMail"));
				noticeMessage.setReceiverPhone(rs.getString("ReceiverPhone"));
				noticeMessage.setMessageText(rs.getString("MessageText"));
				noticeMessage.setIsSend(rs.getInt("IsSend"));
				noticeMessage.setSendTimes(rs.getInt("SendTimes"));
				noticeMessage.setTimeSpan(rs.getInt("TimeSpan"));
				noticeMessage.setHasSendTimes(rs.getInt("HasSendTimes"));
				noticeMessage.setLastSendTime(rs.getTimestamp("LastSendTime"));
				noticeMessage.setNextSendTime(rs.getTimestamp("NextSendTime"));
				noticeMessage.setStatus(rs.getString("Status"));
				noticeMessage.setVersion(rs.getInt("Version"));
				noticeMessage.setCompanyCode(rs.getString("CompanyCode"));
				noticeMessage.setMsgTitle(rs.getString("MsgTitle"));
				list.add(noticeMessage);
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return list;
	}
	
	public NoticeMessage queryNoticeMessageById(int msgId) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		NoticeMessage noticeMessage = null;
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select top 1 * from crc_NoticeMessage cnt where cnt.SendTimes>cnt.HasSendTimes and cnt.NextSendTime<(select getdate() as today) and NoticeCategory in('Email','Message')  and MsgId=? ");
			pstmt.setInt(1, msgId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				noticeMessage = new NoticeMessage();
				noticeMessage.setMsgId(rs.getInt("MsgId"));
				noticeMessage.setNoticeCategory(rs.getString("NoticeCategory"));
				noticeMessage.setMsgType(rs.getString("msgType"));
				noticeMessage.setSender(rs.getString("Sender"));
				noticeMessage.setReceiverId(rs.getString("ReceiverId"));
				noticeMessage.setReceiverName(rs.getString("ReceiverName"));
				noticeMessage.setReceiverMail(rs.getString("ReceiverMail"));
				noticeMessage.setReceiverPhone(rs.getString("ReceiverPhone"));
				noticeMessage.setMessageText(rs.getString("MessageText"));
				noticeMessage.setIsSend(rs.getInt("IsSend"));
				noticeMessage.setSendTimes(rs.getInt("SendTimes"));
				noticeMessage.setTimeSpan(rs.getInt("TimeSpan"));
				noticeMessage.setHasSendTimes(rs.getInt("HasSendTimes"));
				noticeMessage.setLastSendTime(rs.getTimestamp("LastSendTime"));
				noticeMessage.setNextSendTime(rs.getTimestamp("NextSendTime"));
				noticeMessage.setStatus(rs.getString("Status"));
				noticeMessage.setVersion(rs.getInt("Version"));
				noticeMessage.setCompanyCode(rs.getString("CompanyCode"));
				noticeMessage.setMsgTitle(rs.getString("MsgTitle"));
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return noticeMessage;
	}

	public void updateSendNoticeMessage(NoticeMessage noticeMessage) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("update crc_NoticeMessage set HasSendTimes=?,NextSendTime=?,LastSendTime=?,IsSend=?,ReturnMsgCode=? where MsgId=?");
			int hassendtime = noticeMessage.getHasSendTimes() + 1;
			Timestamp nextsendTime = noticeMessage.getNextSendTime();
			Calendar cal = Calendar.getInstance();
			cal.setTime(nextsendTime);
			cal.add(Calendar.MINUTE, noticeMessage.getTimeSpan());
			Timestamp lasTimestamp = new Timestamp(new Date().getTime());
			int issend = 0;
			if (hassendtime != noticeMessage.getSendTimes()) {
				nextsendTime = new Timestamp(cal.getTime().getTime());
			} else {
				issend = 1;
			}
			pstmt.setInt(1, hassendtime);
			pstmt.setTimestamp(2, nextsendTime);
			pstmt.setTimestamp(3, lasTimestamp);
			pstmt.setInt(4, issend);
			pstmt.setString(5, noticeMessage.getReturnMsgCode());
			pstmt.setInt(6, noticeMessage.getMsgId());
			pstmt.execute();
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}
	
	public String getCompanyCode(String gpsno) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String companyCode = "";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select CompanyCode from crc_GPS where GPSNO = ? ");
			pstmt.setString(1, gpsno);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				companyCode = rs.getString("CompanyCode");
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return companyCode;
	}
	
	public String getDriverNames(String GPSNO) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String name = "";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con
					.prepareStatement("select ISNULL((SELECT TOP 1 STUFF((SELECT ',' + name FROM crc_Person  WHERE"
							+ " CHARINDEX(CONVERT(VARCHAR(MAX),PersonId) + ',',e.DriverId + ',')> 0"
							+ " FOR XML PATH('')),1,1,'')),'') AS DriverName  from crc_Equipment e where e.GPSNO= ? ");
			pstmt.setString(1, GPSNO);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				name = name + rs.getString("DriverName") + " ";
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				rs.close();
				pstmt.close();
				con.close();
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return name;
	}	
	
	/***
	 * 获取该车辆型号名字
	 * @return
	 */
	public String getCategoryName(String gpsno){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String categoryName = "";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("SELECT rec.CategoryName FROM crc_Equipment ce, ref_EquipmentCategory rec where ce.EquipmentCategoryCode=rec.CategoryCode and ce.GPSNO=?");
			pstmt.setString(1, gpsno);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				categoryName = rs.getString("CategoryName");
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return categoryName ;
	}
}
